﻿CREATE PROCEDURE [util].[SetDatabaseUsers](
	@UserMetaDataXml xml
)
AS
BEGIN
	-- parse the user xml 
	DECLARE @docHandle int;
	EXEC sp_xml_preparedocument @docHandle OUTPUT, @UserMetaDataXml;

	-- loop through each user
	DECLARE C_USERS CURSOR STATIC FOR(
		SELECT
			[DatabaseName],
			[LoginName],
			[UserName],
			[SchemaName],
			[RoleName]
		FROM OPENXML(@docHandle, N'/Database/User') 
		WITH (
			[DatabaseName] sysname '../@Name',
			[LoginName] sysname '@LoginName',
			[UserName] sysname '@UserName',
			[SchemaName] sysname '@SchemaName',
			[RoleName] sysname '@RoleName'
		)
	)
	OPEN C_USERS

	DECLARE @DatabaseName sysname, @LoginName sysname, @UserName sysname, @SchemaName sysname, @RoleName sysname;

	FETCH NEXT FROM C_USERS
		INTO @DatabaseName, @LoginName, @UserName, @SchemaName, @RoleName;

	WHILE(@@FETCH_STATUS = 0)
		BEGIN
			DECLARE @UserExists bit = 0;
			DECLARE @ExistsQuery nvarchar(2000) =
				N'SET @UserExists = (SELECT COUNT(*) FROM [' + @DatabaseName + N'].[sys].[database_principals]
				WHERE ([type] IN (''G'',''U'')) AND ([Name] = @UserName))';
			
			EXEC sp_executesql @ExistsQuery, N'@UserName sysname, @UserExists bit OUTPUT', @UserName, @UserExists OUTPUT;
			
			IF (@UserExists = 0)
				BEGIN
					DECLARE @CreateUserStatement nvarchar(2000) =
						N'EXEC [' + @DatabaseName + N']..sp_executesql N''
							CREATE USER [' + @UserName + ']
							FROM LOGIN [' + @LoginName + ']
							WITH DEFAULT_SCHEMA = [' + @SchemaName + ']'';';
					EXEC(@CreateUserStatement);

					DECLARE @GrantConnectStatement nvarchar(2000) =
						N'EXEC [' + @DatabaseName + N']..sp_executesql N''
							GRANT CONNECT TO [' + @UserName + ']'';';
					EXEC(@GrantConnectStatement);

					DECLARE @AddRoleMemberStatement nvarchar(2000) =
						N'EXEC [' + @DatabaseName + N']..sp_executesql N''
							EXEC sp_addrolemember ''''' + @RoleName + N''''', ''''' + @UserName + N''''''';';
					EXEC(@AddRoleMemberStatement);
				END
			ELSE
				PRINT N'User [' + @UserName + N'] exists in database [' + @DatabaseName + N']';

			FETCH NEXT FROM C_USERS
				INTO @DatabaseName, @LoginName, @UserName, @SchemaName, @RoleName;
		END
	CLOSE C_USERS
	DEALLOCATE C_USERS

	-- cleanup xml
	EXEC sp_xml_removedocument @docHandle;
END